import mysql.connector
from docx import Document

def extract_questions_and_answers(file_path):
    document = Document(file_path)
    questions = []
    current_question = None

    for para in document.paragraphs:
        text = para.text.strip()

        # Identify and process questions
        if text.startswith("Q:"):
            if current_question:
                questions.append(current_question)
            current_question = {'text': text[2:].strip(), 'options': {'A': None, 'B': None, 'C': None, 'D': None}, 'correct_answer': None}

        # Identify options
        elif text.startswith("A:"):
            if current_question is not None:
                current_question['options']['A'] = text[2:].strip()
        elif text.startswith("B:"):
            if current_question is not None:
                current_question['options']['B'] = text[2:].strip()
        elif text.startswith("C:"):
            if current_question is not None:
                current_question['options']['C'] = text[2:].strip()
        elif text.startswith("D:"):
            if current_question is not None:
                current_question['options']['D'] = text[2:].strip()

        # Identify correct answer
        elif text.startswith("Answer:"):
            if current_question is not None:
                current_question['correct_answer'] = text[7:].strip()

    # Add the last question if it exists
    if current_question:
        questions.append(current_question)

    return questions

def insert_exam_with_questions_and_answers(exam_name, questions):
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="3306",  # 确保密码正确
        database="school",
        charset='utf8mb4'  # 确保使用 utf8mb4 字符集
    )

    # Use buffered cursors to avoid Unread result errors
    cursor_exam = conn.cursor(buffered=True)
    cursor_question = conn.cursor(buffered=True)

    try:
        # Check if the exam already exists
        cursor_exam.execute("SELECT id FROM exams WHERE name = %s", (exam_name,))
        result = cursor_exam.fetchone()  # Fetch the result to avoid Unread result found
        if result:
            exam_id = result[0]
        else:
            # Insert exam information if not exists
            sql = "INSERT INTO exams (name) VALUES (%s)"
            cursor_exam.execute(sql, (exam_name,))
            exam_id = cursor_exam.lastrowid

        for question in questions:
            # Check if the question already exists
            cursor_question.execute(
                """
                SELECT id FROM questions 
                WHERE exam_id = %s AND question_text = %s AND correct_answer = %s
                """,
                (exam_id, question['text'], question['correct_answer'])
            )
            result = cursor_question.fetchone()  # Fetch the result to avoid Unread result found
            if result:
                continue  # Skip if the question already exists

            # Insert question with options
            sql = """
            INSERT INTO questions (exam_id, question_text, correct_answer, option_a, option_b, option_c, option_d)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            values = (exam_id, question['text'], question['correct_answer'],
                      question['options']['A'], question['options']['B'],
                      question['options']['C'], question['options']['D'])
            cursor_question.execute(sql, values)

        conn.commit()
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        conn.rollback()
    finally:
        cursor_exam.close()
        cursor_question.close()
        conn.close()

# 使用示例
file_path = 'C:\\Users\\颜心怡\\Desktop\\试题库.docx'
exam_name = 'Sample Exam'

# 提取题目和答案
questions = extract_questions_and_answers(file_path)

# 将题目和答案上传到数据库
insert_exam_with_questions_and_answers(exam_name, questions)